package br.unicamp.ic.lis.docseaservice.model.dao;

import java.util.List;

import org.apache.commons.dbutils.handlers.BeanHandler;

import br.unicamp.ic.lis.docseaservice.model.db.QueryHandler;
import br.unicamp.ic.lis.docseaservice.model.queryresult.LocationUriListHandler;
import br.unicamp.ic.lis.docseaservice.model.vo.LocationUri;

import com.vividsolutions.jts.geom.Point;
import com.vividsolutions.jts.geom.Polygon;

public class LocationUriDAO implements ILocationUriDAO {

	public static final String TEMPLATE_INSERT = "INSERT INTO location_uri(location, uri, country, state, city) VALUES (ST_GeometryFromText('%s',%d), '%s', '%s','%s','%s');";
	public static final String TEMPLATE_GET_BY_ID = "SELECT * FROM location_uri WHERE id=?;";
	public static final String TEMPLATE_GET_BY_POLYGON = "SELECT * FROM location_uri WHERE contains(ST_GeometryFromText('%s',%d), location_uri.location)";
	public static final String TEMPLATE_DELETE = "DELETE FROM location_uri WHERE location=ST_GeometryFromText('%s',%d) AND uri='%s';";

	public static final String TEMPLATE_UPDATE_LOCATION = "UPDATE location_uri SET location = ST_GeometryFromText('%s',%d) WHERE id = %d";
	public static final String TEMPLATE_UPDATE_URI = "UPDATE location_uri SET uri = '%s' WHERE id = %d";

	@Override
	public LocationUri get(Long id) {
		QueryHandler qh = new QueryHandler();
		BeanHandler<LocationUri> beanHandler = new BeanHandler<LocationUri>(
				LocationUri.class);
		return qh.execute(TEMPLATE_GET_BY_ID, beanHandler, id);
	}

	@Override
	public void add(LocationUri t) {
		QueryHandler qh = new QueryHandler();
		qh.execute(String.format(TEMPLATE_INSERT, t.getLocation(), t
				.getLocation().getSRID(), t.getUri(), t.getCountry(), t
				.getState(), t.getCity()));
	}

	@Override
	public void update(LocationUri t) {
		// TODO Auto-generated method stub

	}

	@Override
	public void delete(LocationUri t) {
		QueryHandler qh = new QueryHandler();
		if (t.getId() == null) {
			qh.execute(String.format(TEMPLATE_DELETE, t.getLocation(), t
					.getLocation().getSRID(), t.getUri()));
		}

	}

	@Override
	public List<LocationUri> get(Polygon polygon) {
		QueryHandler qh = new QueryHandler();
		return qh.execute(
				String.format(TEMPLATE_GET_BY_POLYGON, polygon,
						polygon.getSRID()), new LocationUriListHandler());
	}

	@Override
	public void deleteAll() {
		QueryHandler qh = new QueryHandler();
		qh.execute("DELETE FROM location_uri");
	}

	@Override
	public void update(Long id, Point newLocation) {
		QueryHandler qh = new QueryHandler();
		qh.execute(String.format(TEMPLATE_UPDATE_LOCATION, newLocation,
				newLocation.getSRID(), id));
	}

	@Override
	public void update(Long id, String newUri) {
		QueryHandler qh = new QueryHandler();
		qh.execute(String.format(TEMPLATE_UPDATE_URI, newUri, id));
	}
}
